<?php
include_once 'Modelo/BD_MySQL.php';

class DA_BD_Reporte{
	private $bd = null;
	private $error = null;
	
	public function DA_BD_Reporte($arrReportes,$arrApps,$arrVentanas,$arrReporteLineas) {
		$this->bd = BD_MySQL::getInstance();
		$this->bd->connect();
		if(!$this->bd->hayError()){
			$this->insertarReportes($arrReportes);
			if(!$this->bd->hayError()){
				$this->insertarAplicaciones($arrApps);
				if(!$this->bd->hayError()){
					$this->insertarVentanas($arrApps,$arrVentanas);
					if(!$this->bd->hayError()){
						$this->insertarReportelinea($arrReportes,$arrApps,$arrVentanas,$arrReporteLineas);						
					}	
				}
			}
		}
		$this->error = $this->bd->getError();
		$this->bd->close();		
	}

	private function insertarReportes(&$arrReportes){
		foreach ($arrReportes as &$reporte) {
			$sql="SELECT `idReporte` FROM `reporte` WHERE `nomArch`='".$reporte['nomArch']."'";
			$row = $this->bd->getOneRowByQueryString($sql);
			if(!$this->bd->hayError()){
				if($this->bd->getAffectedRowsBySelect()>0){
					$reporte['idReporteBD'] = $row['idReporte'];
				}
				else{
					$row = array(
						'nomArch'=>$reporte['nomArch'],
						'fecha'=>$this->bd->formatDate($reporte['fecha'],'DD/MM/YYYY'),
						'idComputer'=>$reporte['idComputer'],
						'user'=>$reporte['user']
					);			
					$reporte['idReporteBD'] = $this->bd->insert('reporte', $row);
					if($this->bd->hayError()){
						$this->error = $this->bd->getError();
						break;
					}		
				}				
			}
			else{
				$this->error = $this->bd->getError();
				break;								
			}			
		}
	}	
	
	private function insertarAplicaciones(&$arrApps){
		foreach ($arrApps as $key => &$app) {
			$sql="SELECT `idAplicacion` FROM `aplicacion` WHERE `nombre`='".$this->bd->escape($key)."'";
			$row = $this->bd->getOneRowByQueryString($sql);
			if(!$this->bd->hayError()){
				if($this->bd->getAffectedRowsBySelect()>0){
					$app['idAppBD'] = $row['idAplicacion'];
				}
				else{
					$row = array('nombre'=>$key);			
					$app['idAppBD'] = $this->bd->insert('aplicacion', $row);
					if($this->bd->hayError()){
						$this->error = $this->bd->getError();
						break;
					}		
				}
			}
			else{
				$this->error = $this->bd->getError();
				break;								
			}			
		}
	}
	
	private function insertarVentanas($arrApps,&$arrVentanas){
		foreach ($arrVentanas as &$ventana) {			
			$ventana['idAppBD'] = $arrApps[$ventana['nomApp']]['idAppBD'];
			$sql ="SELECT `idVentana` FROM `ventana` WHERE ";
			$sql.="`idAplicacion`=".$ventana['idAppBD']." and ";
			$sql.="`titulo`='".$this->bd->escape($ventana['titulo'])."'";
			$row = $this->bd->getOneRowByQueryString($sql);			
			if(!$this->bd->hayError()){
				if($this->bd->getAffectedRowsBySelect()>0){
					$ventana['idVentanaBD'] = $row['idVentana'];
				}
				else{
					$row = array(
						'idAplicacion'=>$ventana['idAppBD'],
						'titulo'=>$ventana['titulo']
					);			
					$ventana['idVentanaBD'] = $this->bd->insert('ventana', $row);
					if($this->bd->hayError()){
						$this->error = $this->bd->getError();
						break;
					}		
				}				
			}
			else{
				$this->error = $this->bd->getError();
				break;								
			}
		}		
	}
	
	private function insertarReportelinea($arrReportes,$arrApps,$arrVentanas,&$arrReporteLineas){
		foreach ($arrReporteLineas as &$reporteLinea) {	
			$reporteLinea['idReporteBD'] = $arrReportes[$reporteLinea['idReporteLocal']]['idReporteBD'];
			$reporteLinea['idVentanaBD'] = $arrVentanas[$reporteLinea['lineaTitulo']]['idVentanaBD'];
			$row = array(
				'idReporte'=>$reporteLinea['idReporteBD'],
				'idVentana'=>$reporteLinea['idVentanaBD'],
				'hora'=>$reporteLinea['hora'],
				'contenido'=>$reporteLinea['contenido']
			);			
			$reporteLinea['idReporteLineaBD'] = $this->bd->insert('reportelinea', $row);
			if($this->bd->hayError()){
				$this->error = $this->bd->getError();
				break;
			}			
		}
	}
	
	private static function getSelectorDeFecha($selector,$strDate) {
		$arrStrDate = explode('/',$strDate);
		$fecha = "'".$arrStrDate[2].'-'.$arrStrDate[1].'-'.$arrStrDate[0]."'";
		switch ($selector) {
			case 'btnCalHoy':
			case 'btnCalPorDia':
				return 'R.fecha = ' . $fecha;
			break;
			case 'btnCalPorMes':
				return 'MONTH(R.fecha) = MONTH(' . $fecha . ')';						
			break;
			case 'btnCalPorAno':
				return 'YEAR(R.fecha) = YEAR(' . $fecha . ')';
			break;
			default:
				return false;
			break;
		}			
	}
	
	private static function getDataFromBD_BySql($sql) {
		$bd = BD_MySQL::getInstance();
		$bd->connect();
		$rows = $bd->getAllRowsByQueryString($sql);
		$bd->close();
		return array(
			'rows'=>$rows,
			'error'=>$bd->getError(),
		);
	}
	
	public static function selectAppsByDate($rangoFechas,$strDate) {
		$sql = 'select * from aplicacion A ';
		if($rangoFechas != 'btnCalTodas'){
			$sql.= 'inner join ventana V on A.idAplicacion = V.idVentana
					inner join reportelinea RL on V.idVentana = RL.idVentana
					inner join reporte R on R.idReporte = RL.idReporte
					where ';
			$selectorSql = self::getSelectorDeFecha($rangoFechas,$strDate);
			if($selectorSql !== false){
				$sql.= $selectorSql;
			}
			else{
				return false;
			}
		}		
		return self::getDataFromBD_BySql($sql);
	}
	
	public static function selectWinsByDateByApp($rangoFechas,$strDate,$idApp) {
		$sql = 'select * from ventana V ';
		if($rangoFechas != 'btnCalTodas'){
			$sql.= 'inner join aplicacion A on A.idAplicacion = V.idVentana
					inner join reportelinea RL on V.idVentana = RL.idVentana
					inner join reporte R on R.idReporte = RL.idReporte			
					where ';
			$selectorSql = self::getSelectorDeFecha($rangoFechas,$strDate);
			if($selectorSql !== false){
				$sql.= $selectorSql. ' and A.idAplicacion ='. $idApp;
			}
			else{
				return false;
			}
		}		
		return self::getDataFromBD_BySql($sql);
	}
	
	public function hayError() {
		return $this->error->hayError();
	}
	public function getError() {
		return $this->error;
	}
		
}